/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.toko.dao.impl;

import com.toko.dao.ItemDAO;
import com.toko.model.Item;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Benny
 */
public class ItemDAOImpl implements ItemDAO {

    private Connection conn;

    public ItemDAOImpl(Connection conn) {
        this.conn = conn;
    }

    @Override
    public void InsertItem(Item item) throws Exception {
        String sql = "INSERT INTO item(id,name,description,price,categories_id) "
                + "VALUES(?,?,?,?,?)";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setInt(1, item.getId());
        st.setString(2, item.getName());
        st.setString(3, item.getDescription());
        st.setDouble(4, item.getPrice());
        st.setLong(5, item.getCategory().getId());
        st.executeUpdate();
        st.close();
    }

    @Override
    public void DeleteItem(int id) throws Exception {
        String sql = "DELETE FROM item WHERE id=?";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setLong(1, id);
        st.executeUpdate();
        st.close();
    }

    @Override
    public Item getById(int id) throws Exception {
         Item item = null;
        String sql = "SELECT * FROM " +
                "item WHERE id=?";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setInt(1, id);
        ResultSet rs = st.executeQuery();
        if(rs.next()){
            item = new Item();
            item.setId(rs.getInt(1));
            item.setName(rs.getString(2));
            item.setDescription(rs.getString(3));
            item.setPrice(rs.getDouble(4));   
            item.setCategory((new CategoryDAOImpl(this.conn)).getById(rs.getInt(5)));
        }
        return item;
    }

    @Override
    public List<Item> getAll() throws Exception {
        List<Item> items = new ArrayList<Item>();
        String sql = "SELECT * FROM item";
        PreparedStatement st = this.conn.prepareStatement(sql);
        ResultSet rs = st.executeQuery();        
        while(rs.next()){
            Item item = new Item();
            item.setId(rs.getInt(1));
            item.setName(rs.getString(2));
            item.setDescription(rs.getString(3));
            item.setPrice(rs.getDouble(4));
            item.setCategory((new CategoryDAOImpl(this.conn)).getById(rs.getInt(5)));              
            items.add(item);
        }
        return items;
    }

    @Override
    public void UpdateItem(int oldId, Item item) throws Exception {
      String sql = "UPDATE item SET name=?,description=?,price=?,categories_id=? " +
                "WHERE id=?";
        PreparedStatement st = this.conn.prepareStatement(sql);
        st.setString(1, item.getName());
        st.setString(2, item.getDescription());
        st.setLong(3, item.getCategory().getId());
        st.setDouble(4, item.getPrice());
        st.setLong(5, oldId);
        st.executeUpdate();
        st.close();
    }
}
